package com.wind.trademark.bill.dao.impl;

import java.util.Map;

import org.apache.commons.lang.StringUtils;

public class BillSql {
	

	public static String queryBillSQL(Map<String, Object> params) {
		StringBuffer stringBuffer= new StringBuffer();
		stringBuffer.append("   SELECT    ");
		stringBuffer.append("   	bl_bill.*, ct_company.NAME compName,   ");
		stringBuffer.append("   	tk_task.TASK_NAME,   ");
		stringBuffer.append("   	tk_task. OWNER,   ");
		stringBuffer.append("   	acc_user. NAME OWNER_NAME,   ");
		stringBuffer.append("   	CONCAT(   ");
		stringBuffer.append("   		'案件 : ',   ");
		stringBuffer.append("   		tk_task.TASK_NAME,   ");
		stringBuffer.append("   		', 公司 : ',   ");
		stringBuffer.append("   		ct_company.NAME,   ");
		stringBuffer.append("   		', 所属律师 : ',   ");
		stringBuffer.append("   		acc_user. NAME   ");
		stringBuffer.append("   	)group_info,   ");
		stringBuffer.append("   	IFNULL(detail_amount.amount,0) total,   ");
		stringBuffer.append("   	IFNULL(fee_amount.amount,0) paid,   ");
		stringBuffer.append("   	IFNULL(detail_amount.amount,0) - IFNULL(fee_amount.amount,0) un_paid   ");
		stringBuffer.append("   FROM   ");
		stringBuffer.append("   	ct_company ct_company,   ");
		stringBuffer.append("   	tk_task tk_task,   ");
		stringBuffer.append("   	acc_user,   ");
		stringBuffer.append("   	bl_bill bl_bill   ");
		stringBuffer.append("   LEFT JOIN(   ");
		stringBuffer.append("   	SELECT   ");
		stringBuffer.append("   		SUM(bl_bill_detail.amount)amount,   ");
		stringBuffer.append("   		bl_bill_detail.BILL_ID BILL_ID   ");
		stringBuffer.append("   	FROM   ");
		stringBuffer.append("   		bl_bill_detail bl_bill_detail   ");
		stringBuffer.append("   	GROUP BY   ");
		stringBuffer.append("   		bl_bill_detail.BILL_ID   ");
		stringBuffer.append("   )detail_amount ON detail_amount.BILL_ID = bl_bill.ID   ");
		stringBuffer.append("   LEFT JOIN(   ");
		stringBuffer.append("   	SELECT   ");
		stringBuffer.append("   		SUM(bl_fee.amount)amount,   ");
		stringBuffer.append("   		bl_fee.BILL_ID BILL_ID   ");
		stringBuffer.append("   	FROM   ");
		stringBuffer.append("		bl_fee bl_fee   ");
		stringBuffer.append("	GROUP BY   ");
		stringBuffer.append("		bl_fee.BILL_ID   ");
		stringBuffer.append(")fee_amount ON fee_amount.BILL_ID = bl_bill.ID   ");
		stringBuffer.append("WHERE   ");
		stringBuffer.append("	bl_bill.TASK_ID = tk_task.ID   ");
		stringBuffer.append("AND tk_task.COMP_ID = ct_company.ID   ");
		stringBuffer.append("AND acc_user.ID = tk_task.OWNER ");

		if(StringUtils.isNotEmpty((String)params.get("code"))){
			stringBuffer.append("AND LOWER(bl_bill.CODE) LIKE:code ");
			params.put("code", "%"+params.get("code").toString().toLowerCase()+ "%");

		}
		if(StringUtils.isNotEmpty((String)params.get("taskName"))){
			stringBuffer.append("AND LOWER(tk_task.TASK_NAME) LIKE :taskName ");
			params.put("taskName", "%"+params.get("taskName").toString().toLowerCase()+ "%");
		}
		if(StringUtils.isNotEmpty((String) params.get("owner"))){
			stringBuffer.append("AND tk_task.OWNER =:owner ");		
		}
		if(StringUtils.isNotEmpty((String) params.get("compId"))){
			stringBuffer.append("AND ct_company.ID =:compId ");		
		}
		if(StringUtils.isNotEmpty((String) params.get("status"))){
			stringBuffer.append("AND bl_bill.STATUS =:status ");		
		}
		if(StringUtils.isNotEmpty((String) params.get("deadlineStartDate"))){
			stringBuffer.append("AND bl_bill.DEADLINE >=:deadlineStartDate ");		
		}
		if(StringUtils.isNotEmpty((String) params.get("deadlineEndDate"))){
			stringBuffer.append("AND bl_bill.DEADLINE <=:deadlineEndDate ");		
		}
		stringBuffer.append("ORDER BY bl_bill.CODE ");
		return stringBuffer.toString();
	}

	public static String queryBillDetail(Map<String, Object> params) {
		return "SELECT bl_bill_detail.*  FROM bl_bill_detail bl_bill_detail WHERE bl_bill_detail.BILL_ID=:billId ORDER BY bl_bill_detail.ID DESC";
	}

	public static String deleteBillDetails() {
		StringBuffer stringBuffer= new StringBuffer();
		stringBuffer.append("DELETE ");
		stringBuffer.append("FROM bl_bill_detail  ");
		stringBuffer.append("WHERE id IN (:ids) ");
		return stringBuffer.toString();
	}
	public static String deleteFees() {
		StringBuffer stringBuffer= new StringBuffer();
		stringBuffer.append("DELETE ");
		stringBuffer.append("FROM bl_fee  ");
		stringBuffer.append("WHERE id IN (:ids) ");
		return stringBuffer.toString();
	}
	
	public static String queryBillVo() {
		StringBuffer stringBuffer= new StringBuffer();
		stringBuffer.append("SELECT ");
		stringBuffer.append("bl_bill.*, create_user.`NAME` CREATE_NAME, ");
		stringBuffer.append("update_user.`NAME` LAST_UPDATE_NAME ");
		stringBuffer.append("FROM ");
		stringBuffer.append("bl_bill bl_bill, ");
		stringBuffer.append("acc_user create_user, ");
		stringBuffer.append("acc_user update_user ");
		stringBuffer.append("WHERE ");
		stringBuffer.append("	bl_bill.CREATE_BY = create_user.ID ");
		stringBuffer.append("AND bl_bill.LAST_UPDATE_BY = update_user.ID ");
		stringBuffer.append("AND bl_bill.TASK_ID = :taskId ");
		return stringBuffer.toString();
	}


	public static String queryFeeVo() {
		StringBuffer stringBuffer= new StringBuffer();
		stringBuffer.append("SELECT ");
		stringBuffer.append("bl_fee.*,bl_fee.id bl_fee_id, create_user.`NAME` CREATE_NAME, ");
		stringBuffer.append("update_user.`NAME` LAST_UPDATE_NAME ");
		stringBuffer.append("FROM ");
		stringBuffer.append("bl_fee bl_fee, ");
		stringBuffer.append("acc_user create_user, ");
		stringBuffer.append("acc_user update_user ");
		stringBuffer.append("WHERE ");
		stringBuffer.append("	bl_fee.CREATE_BY = create_user.ID ");
		stringBuffer.append("AND bl_fee.LAST_UPDATE_BY = update_user.ID ");
		stringBuffer.append("AND bl_fee.BILL_ID = :billId ");
		stringBuffer.append("ORDER BY bl_fee.id DESC");
		return stringBuffer.toString();
	}
	public static String queryDetailVo() {
		StringBuffer stringBuffer= new StringBuffer();
		stringBuffer.append("SELECT ");
		stringBuffer.append("bl_bill_detail.*,bl_bill_detail.id bill_detail_id, create_user.`NAME` CREATE_NAME, ");
		stringBuffer.append("update_user.`NAME` LAST_UPDATE_NAME ");
		stringBuffer.append("FROM ");
		stringBuffer.append("bl_bill_detail bl_bill_detail, ");
		stringBuffer.append("acc_user create_user, ");
		stringBuffer.append("acc_user update_user ");
		stringBuffer.append("WHERE ");
		stringBuffer.append("	bl_bill_detail.CREATE_BY = create_user.ID ");
		stringBuffer.append("AND bl_bill_detail.LAST_UPDATE_BY = update_user.ID ");
		stringBuffer.append("AND bl_bill_detail.BILL_ID = :billId ");
		stringBuffer.append("ORDER BY bl_bill_detail.id DESC");
		return stringBuffer.toString();
	}

	public static String getBillInfo() {
		String sql="SELECT bl_bill.*,bl_bill.id BILL_ID,acc_user.NAME CREATE_NAME FROM bl_bill bl_bill,acc_user acc_user WHERE bl_bill.CREATE_BY=acc_user.ID AND bl_bill.ID=:billId";
		return sql;
	}

	public static String getTaskInfo() {
		String sql="SELECT tk_task.*,tk_task.ID TASK_ID,create_user.NAME CREATE_NAME,create_user.CREATE_TIME CREATE_TIME,owner_user.NAME OWNER_NAME  FROM tk_task tk_task,acc_user create_user,acc_user owner_user WHERE tk_task.CREATE_BY=create_user.ID AND tk_task.`OWNER`= owner_user.id and tk_task.ID=:taskId";
		return sql;
	}
	public static String getCompInfo() {
		String sql="SELECT ct_company.ID COMP_ID,ct_company.NAME  compName ,ct_contactor.NAME CONTACTOR_NAME,ct_contactor.TEL CONTACTOR_TEL,ct_contactor.MOBILE CONTACTOR_MOBILE,ct_contactor.EMAIL CONTACTOR_EMAIL,ct_contactor.FAX CONTACTOR_FAX FROM ct_company ct_company LEFT  JOIN  ct_contactor ct_contactor  ON ct_company.ID=ct_contactor.COMP_ID   where ct_contactor.ISDEFAULT='Y' AND ct_company.ID=:compId";
		return sql;
	}

	public static String getTotalAmountByBillId() {
		String sql="SELECT SUM(bl_bill_detail.amount) total FROM  bl_bill_detail bl_bill_detail WHERE  bl_bill_detail.BILL_ID= ? GROUP BY bl_bill_detail.BILL_ID";
		return sql;
	}

	public static String getPaidAmountByBillId() {
		String sql="SELECT SUM(bl_fee.amount) paid FROM  bl_fee bl_fee WHERE bl_fee.BILL_ID=  ?  GROUP BY bl_fee.BILL_ID";
		return sql;
	}
	
	
	
}
